/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package daos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.*;
import util.WebConst;

/**
 *
 * @author trietto
 */
public class ItemDAO {

    private PreparedStatement pStmtSelectCount = null;
    private PreparedStatement pStmtSelectAll = null;
    private PreparedStatement pStmtSelectAllPerPage = null;
    private PreparedStatement pStmtSelectRecommend = null;
    private PreparedStatement pStmtSearch = null;
    private PreparedStatement pStmtSearchCount = null;
    private PreparedStatement pStmtSelect = null;
    private PreparedStatement pStmtInsert = null;
    private PreparedStatement pStmtDelete = null;
    private PreparedStatement pStmtUpdate = null;
    private static ItemDAO itemDAO = null;

    public static ItemDAO singleton() {
        if (itemDAO == null) {
            itemDAO = new ItemDAO();
        }
        return itemDAO;
    }

    public ItemDAO() {
        try {
            pStmtSearch = DBConnection.getConnection().prepareStatement("SELECT * FROM Items WHERE CategoryId=? AND Name LIKE ? LIMIT ?,?");
            pStmtSearchCount = DBConnection.getConnection().prepareStatement("SELECT Count(ItemId) FROM Items WHERE CategoryId=? AND Name LIKE ?");
            pStmtSelectCount = DBConnection.getConnection().prepareStatement("SELECT Count(ItemId) FROM Items");
            pStmtSelectAll = DBConnection.getConnection().prepareStatement("SELECT * FROM Items");
            pStmtSelectAllPerPage = DBConnection.getConnection().prepareStatement("SELECT * FROM Items LIMIT ?,?");
            pStmtSelectRecommend = DBConnection.getConnection().prepareStatement("SELECT * FROM Items WHERE IsRecommend=1");
            pStmtSelect = DBConnection.getConnection().prepareStatement("SELECT * FROM Items WHERE ItemId = ?");
            pStmtInsert = DBConnection.getConnection().prepareStatement("INSERT INTO Items(CategoryId, Author,"
                    + " Name, ImageULR, ProductURL, Notes, Price, IsRecommend) VALUES(?,?,?,?,?,?,?,?)");
            pStmtDelete = DBConnection.getConnection().prepareStatement("DELETE FROM Items WHERE ItemId = ?");
            pStmtUpdate = DBConnection.getConnection().prepareStatement("UPDATE Items SET CategoryId=?, Author=?,"
                    + " Name=?, ImageULR=?, ProductURL=?, Notes=?, Price=?, IsRecommend=? WHERE ItemId = ?");
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int recordCount() {
        int totalRecord = 0;
        try {
            ResultSet rs = pStmtSelectCount.executeQuery();
            while (rs.next()) {
                totalRecord = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(CardTypeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalRecord;
    }

    public int searchCount(int categoryId, String name) {
        int totalRecord = 0;
        try {
            pStmtSearchCount.setInt(1, categoryId);
            pStmtSearchCount.setString(2, "%"+ name + "%");
            ResultSet rs = pStmtSearchCount.executeQuery();
            while (rs.next()) {
                totalRecord = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(CardTypeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalRecord;
    }


    public ArrayList<Item> selectAll() {
        ArrayList<Item> lstItems = new ArrayList<Item>();

        try {
            ResultSet rs = pStmtSelectAll.executeQuery();
            while (rs.next()) {
                lstItems.add(new Item(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getDouble(8), rs.getInt(9)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstItems;
    }

    public ArrayList<Item> selectAll(int page) {
        ArrayList<Item> lstItems = new ArrayList<Item>();
        int offset = (page - 1) * WebConst.ROW_PER_PAGE;
        try {
            pStmtSelectAllPerPage.setInt(1, offset);
            pStmtSelectAllPerPage.setInt(2, WebConst.ROW_PER_PAGE);
            ResultSet rs = pStmtSelectAllPerPage.executeQuery();
            while (rs.next()) {
                lstItems.add(new Item(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getDouble(8), rs.getInt(9)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstItems;
    }

        public ArrayList<Item> selectRecommend() {
        ArrayList<Item> lstItems = new ArrayList<Item>();
        try {
            ResultSet rs = pStmtSelectRecommend.executeQuery();
            while (rs.next()) {
                lstItems.add(new Item(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getDouble(8), rs.getInt(9)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstItems;
    }

    public ArrayList<Item> search(int categoryId, String name, int page) {
        ArrayList<Item> lstItems = new ArrayList<Item>();
        int offset = (page - 1) * WebConst.ROW_PER_PAGE;
        try {
            pStmtSearch.setInt(1, categoryId);
            pStmtSearch.setString(2,"%"+ name + "%");
            pStmtSearch.setInt(3, offset);
            pStmtSearch.setInt(4, WebConst.ROW_PER_PAGE);
            ResultSet rs = pStmtSearch.executeQuery();
            while (rs.next()) {
                lstItems.add(new Item(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getDouble(8), rs.getInt(9)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstItems;
    }

    public Item select(int itemId) {
        Item item = null;
        try {
            pStmtSelect.setInt(1, itemId);
            ResultSet rs = pStmtSelect.executeQuery();
            while (rs.next()) {
                item = new Item(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getDouble(8), rs.getInt(9));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return item;
    }

    public String insert(Item item) {
        String result = "";
        boolean bRet = false;
        int i = 1;
        try {
            pStmtInsert.setInt(i++, item.getCategoryId());
            pStmtInsert.setString(i++, item.getAuthor());
            pStmtInsert.setString(i++, item.getName());
            pStmtInsert.setString(i++, item.getImageURL());
            pStmtInsert.setString(i++, item.getProductURL());
            pStmtInsert.setString(i++, item.getNotes());
            pStmtInsert.setDouble(i++, item.getPrice());
            pStmtInsert.setInt(i++, item.getIsRecommended());

            pStmtInsert.execute();
            bRet = true;
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = bRet ? "Successed" : "Failed";
        return result;
    }

    public String delete(int itemId) {
        String result = "";
        int numOfRows = 0;
        try {
            pStmtDelete.setInt(1, itemId);
            numOfRows = pStmtDelete.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }

    public String update(Item item) {
        String result = "";
        int numOfRows = 0;
        int i = 1;
        try {
            pStmtUpdate.setInt(i++, item.getCategoryId());
            pStmtUpdate.setString(i++, item.getAuthor());
            pStmtUpdate.setString(i++, item.getName());
            pStmtUpdate.setString(i++, item.getImageURL());
            pStmtUpdate.setString(i++, item.getProductURL());
            pStmtUpdate.setString(i++, item.getNotes());
            pStmtUpdate.setDouble(i++, item.getPrice());
            pStmtUpdate.setInt(i++, item.getIsRecommended());
            pStmtUpdate.setInt(i++, item.getItemId());
            numOfRows = pStmtUpdate.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ItemDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }
}
